--    Program: 		CAPARIMP_INVOICE_BATCH.sql
--    Description:  Import AR Invoice Batch information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_batch_id				number;
                    		                 		
   cursor cur_invoice_batch is
      select * from CRST_RA_BATCHES_ALL
      --WHERE BATCH_ID = 1419
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_invoice_batch loop   	
     l_err_msg_tmp := '';
     
     select RA_BATCHES_S.NEXTVAL
     into   l_batch_id
     from   dual;
     
     update CRST_RA_BATCHES_ALL
     set    batch_id_n = l_batch_id
     where  batch_id = cur.batch_id;
     
     begin
       -- import data
       insert into RA_BATCHES_ALL 
       (
         BATCH_ID                ,
         LAST_UPDATE_DATE        ,
         LAST_UPDATED_BY         ,
         CREATION_DATE           ,
         CREATED_BY              ,
         LAST_UPDATE_LOGIN       ,
         NAME                    ,
         BATCH_DATE              ,
         GL_DATE                 ,
         STATUS                  ,
         TYPE                    ,
         BATCH_SOURCE_ID         ,
         CONTROL_COUNT           ,
         CONTROL_AMOUNT          ,
         COMMENTS                ,
         ATTRIBUTE_CATEGORY      ,
         ATTRIBUTE1              ,
         ATTRIBUTE2              ,
         ATTRIBUTE3              ,
         ATTRIBUTE4              ,
         ATTRIBUTE5              ,
         ATTRIBUTE6              ,
         ATTRIBUTE7              ,
         ATTRIBUTE8              ,
         ATTRIBUTE9              ,
         ATTRIBUTE10             ,
         REQUEST_ID              ,
         PROGRAM_APPLICATION_ID  ,
         PROGRAM_ID              ,
         PROGRAM_UPDATE_DATE     ,
         CURRENCY_CODE           ,
         EXCHANGE_RATE_TYPE      ,
         EXCHANGE_DATE           ,
         EXCHANGE_RATE           ,
         ATTRIBUTE11             ,
         ATTRIBUTE12             ,
         ATTRIBUTE13             ,
         SET_OF_BOOKS_ID         ,
         ATTRIBUTE14             ,
         ATTRIBUTE15             ,
         ORG_ID                  ,
         PURGED_CHILDREN_FLAG    ,
         ISSUE_DATE              ,
         MATURITY_DATE           ,
         SPECIAL_INSTRUCTIONS    ,
         BATCH_PROCESS_STATUS    ,
         SELECTION_CRITERIA_ID
       )
       values
       (       
         l_batch_id                  ,
         cur.LAST_UPDATE_DATE        ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY), 
         cur.CREATION_DATE           ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY), 
         cur.LAST_UPDATE_LOGIN       ,
         cur.NAME                    ,
         cur.BATCH_DATE              ,
         cur.GL_DATE                 ,
         cur.STATUS                  ,
         cur.TYPE                    ,
         CAPARIMP_MAPPING.GetTransactionSourceIDMapping(cur.ORG_ID, cur.BATCH_SOURCE_ID),
         cur.CONTROL_COUNT           ,
         cur.CONTROL_AMOUNT          ,
         cur.COMMENTS                ,
         cur.ATTRIBUTE_CATEGORY      ,
         cur.ATTRIBUTE1              ,
         cur.ATTRIBUTE2              ,
         cur.ATTRIBUTE3              ,
         cur.ATTRIBUTE4              ,
         cur.ATTRIBUTE5              ,
         cur.ATTRIBUTE6              ,
         cur.ATTRIBUTE7              ,
         cur.ATTRIBUTE8              ,
         cur.ATTRIBUTE9              ,
         cur.ATTRIBUTE10             ,
         cur.REQUEST_ID              ,
         cur.PROGRAM_APPLICATION_ID  ,
         cur.PROGRAM_ID              ,
         cur.PROGRAM_UPDATE_DATE     ,
         cur.CURRENCY_CODE           ,
         cur.EXCHANGE_RATE_TYPE      ,
         cur.EXCHANGE_DATE           ,
         cur.EXCHANGE_RATE           ,
         cur.ATTRIBUTE11             ,
         cur.ATTRIBUTE12             ,
         cur.ATTRIBUTE13             ,
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
         cur.ATTRIBUTE14             ,
         cur.ATTRIBUTE15             ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.PURGED_CHILDREN_FLAG    ,
         cur.ISSUE_DATE              ,
         cur.MATURITY_DATE           ,
         cur.SPECIAL_INSTRUCTIONS    ,
         cur.BATCH_PROCESS_STATUS    ,
         cur.SELECTION_CRITERIA_ID
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_INVOICE_BATCH', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit